package ${project.packageName}.${project.dataCenterType}.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBUtils {

public static final String DBDRIVER = "com.mysql.jdbc.Driver";  
    
    public static final String DBURL = "${project.mysqlUrl}";  
      
    public static final String DBUSER = "${project.mysqlUsername}";  
      
    public static final String DBPASS = "${project.mysqlPassword}";  
      
    public static <#if responseDataType == "list">List<Map<String, Object>></#if><#if responseDataType == "Object">Object</#if> query(String sql) throws Exception{  
          
        Connection conn = null;
          
        Statement stmt = null;
          
        ResultSet result = null;
          
        Class.forName(DBDRIVER);
          
        conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
          
        stmt = conn.createStatement();
          
        result = stmt.executeQuery(sql);
        
        <#if responseDataType == "list">
        List<Map<String, Object>> list = convertList(result);
        </#if>
        
        <#if responseDataType == "object">
        Object obj = convertList(result);
        </#if>
        result.close();  
        stmt.close();  
        conn.close();
        
        <#if responseDataType == "list">
        return list;  
        </#if>
        
        <#if responseDataType == "object">
        return obj;
        </#if>
    }

    private static <#if responseDataType == "list">List<Map<String, Object>></#if><#if responseDataType == "Object">Object</#if> convertList(ResultSet rs) throws SQLException {

        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

        ResultSetMetaData md = rs.getMetaData();

        int columnCount = md.getColumnCount();

        while (rs.next()) { 

        Map<String, Object> rowData = new HashMap<String, Object>();

        for (int i = 1; i <= columnCount; i++) {

        rowData.put(md.getColumnName(i), rs.getObject(i));

        }

        list.add(rowData);

        } 
        <#if responseDataType == "list">
        return list;
		</#if>
		
		<#if responseDataType == "object">	
		return list.get(0);
    	</#if>
    }
}
